﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Linq;
using System.Collections.Generic;
using VietStar.Library.Commom.SqlServer;
using Entity = VietStar.Library.Entity;
namespace VietStar.Library.Data
{
    public class TaiNguyen
    {
        public TaiNguyen() { }
        Connection db = new Connection();
        public DataTable SelectTaiNguyenTheoAction(Entity.TaiNguyen entity)
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_TaiNguyen_SelectTinTucTheoAction";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@Action", SqlDbType.NVarChar, 20).Value = entity.Action;
                SqlCmd.Parameters.Add("@KeySearch", SqlDbType.NVarChar, 200).Value = entity.KeySearch;
                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }
        public bool Insert(Entity.TaiNguyen entity)
        {
            bool boolInsert = false;
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_TaiNguyen_Insert";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@IdDriver", SqlDbType.Int).Value = entity.IdDriver;
                SqlCmd.Parameters.Add("@IdHeDieuHanh", SqlDbType.Int).Value = entity.IdHeDieuHanh;
                SqlCmd.Parameters.Add("@IdNhaSanXuat", SqlDbType.Int).Value = entity.IdNhaSanXuat;
                SqlCmd.Parameters.Add("@TenThietBi", SqlDbType.NVarChar, 100).Value = entity.TenThietBi;
                SqlCmd.Parameters.Add("@HinhAnh", SqlDbType.NVarChar, 250).Value = entity.HinhAnh;
                SqlCmd.Parameters.Add("@NgayDang", SqlDbType.DateTime).Value = entity.NgayDang;
                SqlCmd.Parameters.Add("@NguoiDang", SqlDbType.NVarChar, 50).Value = entity.NguoiDang;
                SqlCmd.Parameters.Add("@Link", SqlDbType.NVarChar, -1).Value = entity.Link;
                SqlCmd.Parameters.Add("@LuotTai", SqlDbType.Int).Value = entity.LuotTai;
                SqlCmd.Parameters.Add("@TrangThai", SqlDbType.Bit).Value = entity.TrangThai;
                SqlCmd.Parameters.Add("@NoiDung", SqlDbType.NVarChar, -1).Value = entity.NoiDung;

                SqlAdp.InsertCommand = SqlCmd;
                SqlAdp.InsertCommand.ExecuteNonQuery();
                SqlCmd.Transaction.Commit();
                boolInsert = true;
                SqlCnn.Close();
            }
            catch
            {
                boolInsert = false;
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return boolInsert;
        }
        public DataTable Select()
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_TaiNguyen_Select";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }
        public DataTable SelectPrimaryKey(Entity.TaiNguyen entity)
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_TaiNguyen_SelectPrimaryKey";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = entity.Id;

                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }
        public DataTable SelectTheoNhaSanXuat(Entity.TaiNguyen entity)
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_TaiNguyen_SelectTheoNhaSanXuat";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@IdNhaSanXuat", SqlDbType.Int).Value = entity.IdNhaSanXuat;

                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }
        public bool Update(Entity.TaiNguyen entity)
        {
            bool boolUpdate = false;
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_TaiNguyen_Update";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = entity.Id;
                SqlCmd.Parameters.Add("@IdDriver", SqlDbType.Int).Value = entity.IdDriver;
                SqlCmd.Parameters.Add("@IdHeDieuHanh", SqlDbType.Int).Value = entity.IdHeDieuHanh;
                SqlCmd.Parameters.Add("@IdNhaSanXuat", SqlDbType.Int).Value = entity.IdNhaSanXuat;
                SqlCmd.Parameters.Add("@TenThietBi", SqlDbType.NVarChar, 100).Value = entity.TenThietBi;
                SqlCmd.Parameters.Add("@HinhAnh", SqlDbType.NVarChar, 250).Value = entity.HinhAnh;
                SqlCmd.Parameters.Add("@NgayDang", SqlDbType.DateTime).Value = entity.NgayDang;
                SqlCmd.Parameters.Add("@NguoiDang", SqlDbType.NVarChar, 50).Value = entity.NguoiDang;
                SqlCmd.Parameters.Add("@Link", SqlDbType.NVarChar, -1).Value = entity.Link;
                SqlCmd.Parameters.Add("@LuotTai", SqlDbType.Int).Value = entity.LuotTai;
                SqlCmd.Parameters.Add("@TrangThai", SqlDbType.Bit).Value = entity.TrangThai;
                SqlCmd.Parameters.Add("@NoiDung", SqlDbType.NVarChar, -1).Value = entity.NoiDung;

                SqlAdp.UpdateCommand = SqlCmd;
                SqlAdp.UpdateCommand.ExecuteNonQuery();
                SqlCmd.Transaction.Commit();
                boolUpdate = true;
                SqlCnn.Close();
            }
            catch
            {
                boolUpdate = false;
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return boolUpdate;
        }
        public bool Delete(Entity.TaiNguyen entity)
        {
            bool boolDelete = false;
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_TaiNguyen_Delete";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = entity.Id;

                SqlAdp.DeleteCommand = SqlCmd;
                SqlAdp.DeleteCommand.ExecuteNonQuery();
                SqlCmd.Transaction.Commit();
                boolDelete = true;
                SqlCnn.Close();
            }
            catch
            {
                boolDelete = false;
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return boolDelete;
        }
        public bool Delete_TheoNhaSanXuat(Entity.TaiNguyen entity)
        {
            bool boolDelete = false;
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_TaiNguyen_Delete_TheoNhaSanXuat";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@IdNhaSanXuat", SqlDbType.Int).Value = entity.IdNhaSanXuat;

                SqlAdp.DeleteCommand = SqlCmd;
                SqlAdp.DeleteCommand.ExecuteNonQuery();
                SqlCmd.Transaction.Commit();
                boolDelete = true;
                SqlCnn.Close();
            }
            catch
            {
                boolDelete = false;
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return boolDelete;
        }
        public DataTable SelectTimKiem(Entity.TaiNguyen entity)
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_TaiNguyen_TimKiem";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@IdDriver", SqlDbType.Int).Value = entity.IdDriver;
                SqlCmd.Parameters.Add("@IdHeDieuHanh", SqlDbType.Int).Value = entity.IdHeDieuHanh;
                SqlCmd.Parameters.Add("@IdNhaSanXuat", SqlDbType.Int).Value = entity.IdNhaSanXuat;
                SqlCmd.Parameters.Add("@Option", SqlDbType.Int).Value = entity.Option;
                SqlCmd.Parameters.Add("@KeySearch", SqlDbType.NVarChar, 100).Value = entity.TenThietBi;
                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }
    }
}
